/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package schedulerManagerment.SeekerManager.jdbc;

import connection.Configuration;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import schedulerManagerment.SeekerManager.entities.Field;
import schedulerManagerment.SeekerManager.entities.Seeker;

/**
 *
 * @author Tngoc
 */
public class Seekers {

    private ArrayList<Seeker> seekerList;
    private Connection conn;

    public Seekers() {
        this.seekerList = new ArrayList<>();
        getInfomation();
    }

    private void getInfomation() {
        try {
            Configuration config = new Configuration();
            conn = config.getConnect();
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("select Seeker.*,Field.[name] as fieldName from Seeker,Field where Field.id=Seeker.fieldID");
            while (rs.next()) {
                Field field = new Field(rs.getInt("fieldID"), rs.getString("fieldName"));
                Seeker sk = new Seeker();
                sk.setId(rs.getInt("ID"));
                sk.setName(rs.getString("name"));
                sk.setDateOfBirth(rs.getDate("dateOfBirth"));
                sk.setGender(rs.getBoolean("gender"));
                sk.setField(field);
                sk.setStatus(rs.getInt("status"));
                sk.setEmail(rs.getString("email"));
                sk.setAddress(rs.getString("address"));
                sk.setPhone(rs.getString("phone"));
                sk.setWebsite(rs.getString("website"));
                sk.setLocation(rs.getString("location"));
                seekerList.add(sk);
            }
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(Seekers.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void refresh() {
        seekerList.clear();
        getInfomation();
    }

    public ArrayList<Seeker> getSeekerList() {
        return seekerList;
    }

    public void setSeekerList(ArrayList<Seeker> seekerList) {
        this.seekerList = seekerList;
    }

    public Seeker getById(int id) {
        for (Seeker seeker : seekerList) {
            if (seeker.getId() == id) {
                return seeker;
            }
        }
        return null;
    }

    public boolean update(int id, String name, String birth, String email, int field, String location, String phone, String address, String gender, String website) {
        try {
            Configuration config = new Configuration();
            conn = config.getConnect();
            String qry = "update Seeker set name = '" + name + "',dateOfBirth='" + birth + "',email='" + email + "',fieldID = " + field + ",location='" + location + "',phone='" + phone + "',address='" + address + "',gender='" + gender + "',website='" + website + "' where ID=" + id;
            Statement st = conn.createStatement();
            st.executeUpdate(qry);
            refresh();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(Seekers.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        return true;
    }

    public void updateSkill(int id,ArrayList<String> qry) {
        try {
            Configuration config = new Configuration();
            conn = config.getConnect();
            Statement st = conn.createStatement();
            Seeker sk = getById(id);
            for (String str : qry) {
            try {
                st.executeUpdate(str);
            } catch (SQLException ex) {
                continue;
            }
            }
            conn.close();
            refresh();
        } catch (SQLException ex) {
            Logger.getLogger(Seekers.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public int insert(String name, String birth, String email, int field, String location, String phone, String address, String gender, String website) {
        try {
            Configuration config = new Configuration();
            conn = config.getConnect();
            String qry = "insert into Seeker values ('" + name + "','" + birth + "','" + gender + "'," + field + ",0,'" + email + "','" + address + "','" + phone + "','" + website + "','" + location + "','')";
            Statement st = conn.createStatement();
            st.execute(qry);
            ResultSet rs = st.executeQuery("select max(ID) from Seeker");
            rs.next();
            refresh();
            conn.close();
            return rs.getInt(1);
        } catch (SQLException ex) {
            Logger.getLogger(Seekers.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        }
    }
}
